package cn.easybuy.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.easybuy.dao.BaseDao;
import cn.easybuy.dao.searchDao;
import cn.easybuy.entity.Page1;
import cn.easybuy.entity.Product;
import cn.easybuy.entity.search;
import cn.easybuy.utils.DataSourceUtil;

public class searchDaoImput extends BaseDao implements searchDao{

	public searchDaoImput(Connection conn) {
		super(conn);
		// TODO Auto-generated constructor stub
	}

	//閿熸枻鎷烽敓鏂ゆ嫹閿熺嫛鍑ゆ嫹閿熺嫛鏂ゆ嫹閿熷彨璇ф嫹璇㈤敓鏂ゆ嫹妯￠敓鏂ゆ嫹閿熺獤顖ゆ嫹閿燂拷
	@Override
	public List<search> seleSearch(search se,Page1 page1,String name) {
		List<search> list=new ArrayList<search>();
		ResultSet rs=null;
		try {
			String sql="SELECT pc.name AS pcName,p.*,pt.price,pt.stock,pt.fileName,pt.isDelete,pt.score,state,pt.specification,pt.colour,pt.description " +
					"FROM easybuy_product p,easybuy_product_category pc,easybuy_product_type pt " +
					"WHERE ((p.categoryLevel1=pc.id AND pc.type=1) OR (p.categoryLevel2=pc.id AND pc.type=2) OR (p.categoryLevel3=pc.id AND pc.type=3)) " +
					"AND pt.typeid=p.id AND pt.state=0 AND ( p.name LIKE ? OR pc.name LIKE ?) GROUP BY pcName LIMIT  ?,?";
			rs = this.executeQuery(sql,"%"+name+"%","%"+name+"%",(page1.getCurrendPage()-1)*page1.getSizePage(),page1.getSizePage());
			while(rs.next()){
				se=new search();
				se.setPcName(rs.getString("pcName"));
				se.setId(rs.getInt("id"));
				se.setName(rs.getString("name"));
				se.setDescription(rs.getString("description"));
				se.setPrice(rs.getInt("price"));
				se.setStock(rs.getInt("stock"));
				se.setCategoryLevel1(rs.getInt("categoryLevel1"));
				se.setCategoryLevel2(rs.getInt("categoryLevel2"));
				se.setCategoryLevel3(rs.getInt("categoryLevel3"));
				se.setFileName(rs.getString("fileName"));
				se.setIsDelete(rs.getInt("isDelete"));
				se.setScore(rs.getInt("score"));
				se.setState(rs.getInt("state"));
				se.setSpecification(rs.getString("specification"));
				se.setColor(rs.getString("colour"));
				list.add(se);
			}
		} catch (Exception e) {
			System.out.println(e);
		}
		
		return list;
	}

	//閿熸枻鎷疯閿熸枻鎷烽敓鏂ゆ嫹閿熸枻鎷�
	@Override
	public int count(String name) {
		int result=0;
		ResultSet rs=null;
		try {
			String sql="SELECT COUNT(*) FROM (SELECT pc.name AS pcName,p.*,pt.price,pt.stock,pt.fileName,pt.isDelete,pt.score,state,pt.specification,pt.colour,pt.description " 
						+"FROM easybuy_product p,easybuy_product_category pc,easybuy_product_type pt "+
						"WHERE ((p.categoryLevel1=pc.id AND pc.type=1) OR (p.categoryLevel2=pc.id AND pc.type=2) OR (p.categoryLevel3=pc.id AND pc.type=3)) "
						+" AND pt.typeid=p.id AND pt.state=0 AND ( p.name LIKE ? OR pc.name LIKE ?) GROUP BY pcName) a";
			rs=this.executeQuery(sql,"%"+name+"%","%"+name+"%");
			if(rs.next()){
				result=rs.getInt(1);
			}
		} catch (Exception e) {
			System.out.println(e);
		}
		return result;
	}

	@Override
	public List<search> listId(int id, Page1 page1) {
		List<search> list=new ArrayList<search>();
		String sql="SELECT id,`name`,price,score FROM easybuy_product  WHERE  categoryLevel3=?  LIMIT ?,?";
		Object [] params={id,(page1.getCurrendPage()-1)*page1.getSizePage(),page1.getSizePage()};
		ResultSet rs=this.executeQuery(sql,params);
		if(rs!=null){
			try {
				while(rs.next()){
					search se=new search();
					se.setId(rs.getInt(1));
					se.setName(rs.getString(2));
					se.setPrice(rs.getInt(3));
					se.setScore(rs.getInt(4));
					list.add(se);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				DataSourceUtil.closeAll(null, null, rs);
			}
		}
		return list;
	}

	@Override
	public int countId(int id) {
		int result=0;
		String sql="SELECT  COUNT(1) FROM  easybuy_product  WHERE  categoryLevel3=?";
		ResultSet rs=this.executeQuery(sql, id);
		if(rs!=null){
		try {
			while(rs.next()){
				result=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		}
		return result;
	}
	
	public List<Product> list(String[] ids) {
		List<Product> list=new ArrayList<Product>();
		StringBuffer sb=new StringBuffer("SELECT  easybuy_product_type.id,`name`,price,score,fileName FROM easybuy_product_type,easybuy_product  WHERE easybuy_product.id=easybuy_product_type.typeid AND  typeid IN  (");
		for(int i=0;i<ids.length;i++){
			if(i==ids.length-1){
				sb.append("?)");
			} else {
				sb.append("?,");
			}
		}
		sb.append(" GROUP BY typeid");
		Object [] params=new Object[ids.length];
		for(int i=0;i<ids.length;i++){
			params[i]=ids[i];
		}
		ResultSet rs=this.executeQuery(sb.toString(), params);
		if(rs!=null){
			try {
				while(rs.next()){
					Product pro=new Product();
					pro.setId(rs.getInt(1));
					pro.setName(rs.getString(2));
					pro.setPrice(rs.getFloat(3));
					pro.setScore(rs.getInt(4));
					pro.setFileName(rs.getString(5));
					list.add(pro);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				DataSourceUtil.closeAll(null, null, rs);
			}
		}
		return list;
	}
}
